package com.util;

import java.util.Random;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class CarDBHelper {
	// 数据库名称
	public static final String DATABASE_NAME = "carshow.db";
	// 数据库版本
	public static final int DATABASE_VERSION = 3;
	// 数据库表名（联系人表）
	public static final String TABLE_CARS = "cars";
	// 数据库表名（组表）
	public static final String TABLE_COUNTRYS = "countrys";
	// 创建表结构（联系人）
	public static final String TABLECARS = "create table cars("
			+ "car_id INTEGER PRIMARY KEY," + // rowID
			"country_name TEXT NOT NULL," + // 所属国家
			"car_brand TEXT," + // 品牌
			"car_name TEXT " + // 车名
			");";
	// 表结构（联系人组）
	public static final String TABLECOUNTRYS = "create table countrys("
			+ "country_id INTEGER PRIMARY KEY," + // rowId
			"country_name TEXT UNIQUE NOT NULL," + // 组名
			"create_time TEXT," + // 创建时间
			"modify_time TEXT" + // 修改时间
			");";
	// 声明context（代表当前活动的对象）
	private Context context;
	// 数据库帮助类
	private DatabaseHelper dbHelper;
	// 声明SQLiteDatabase对象
	private SQLiteDatabase mSQLiteDatabase = null;

	/**
	 * 构造方法
	 * 
	 * @param context
	 */
	public CarDBHelper(Context context) {
		this.context = context;
	}

	/**
	 * 打开数据库连接
	 */
	public void open() {
		dbHelper = new DatabaseHelper(context);
		mSQLiteDatabase = dbHelper.getWritableDatabase();
	}

	/**
	 * 关闭数据库连接
	 */
	public void close() {
		System.out.println("--------------close database-------------");
		dbHelper.close();
	}

	/**
	 * 继承SQLiteOpenHelper帮助类
	 * 
	 * @author 潘建平
	 * 
	 */
	private static class DatabaseHelper extends SQLiteOpenHelper {
		// 构造器(创建数据库) Create a helper object to create, open, and/or manage a
		// database
		public DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		// 创建数据库表
		@Override
		public void onCreate(SQLiteDatabase db) {
			System.out.println("create table start...");
			db.execSQL(TABLECARS);
			db.execSQL(TABLECOUNTRYS);
			// 创建临时的组
			String tempCountrys[] = { "中国", "美国", "日本", "德国", "韩国", "法国", };
			for (int i = 0; i < tempCountrys.length; i++) {
				String sql = "insert into countrys values(?,?,null,null)";
				Object[] bindArgs = { i + 1, tempCountrys[i] };
				db.execSQL(sql, bindArgs);
			}
			// 创建车品牌
			String tempCars[] = { "奥迪", "上海大众", "丰田", "东风日产", "雪佛兰", "宝马",
					"马自达" };

			Random random = new Random();
			int index = 0;
			// 创建临时的车
			for (int i = 0; i <tempCars.length ; i++) {
				String sql = "insert into cars values(?,?,?,null)";
				index = random.nextInt(tempCountrys.length);
				Object[] bindArgs = { i + 1, tempCountrys[index], tempCars[i]};
				db.execSQL(sql, bindArgs);
			}
			System.out.println("create table over...");
		}

		// 版本升级
		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			System.out.println("contactmgr.db Upgrade...");
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_CARS);
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_COUNTRYS);
			onCreate(db);

		}
	}

	// 车表（字段）
	public static final String car_id = "car_id";
	public static final String car_brand = "car_brand";
	public static final String car_name = "car_name";
	String carProjection[] = { car_id, car_brand, car_name };

	// 国家表（字段）
	public static final String country_id = "country_id";
	public static final String country_name = "country_name";
	String countrysProjection[] = { country_id, country_name };

	/**
	 * 查询所有国家
	 * 
	 * @return
	 */
	public Cursor queryAllCountrys() {
		return mSQLiteDatabase.query(TABLE_COUNTRYS, countrysProjection, null,
				null, null, null, null);
	}

	/**
	 * 查询所有车
	 * 
	 * @return
	 */
	public Cursor queryAllCars() {
		return mSQLiteDatabase.query(TABLE_CARS, carProjection, null, null,
				null, null, null);
	}

	/**
	 * 查找给定国家的车辆品牌
	 * 
	 * @param countryName
	 * @return
	 */
	public Cursor getcarsByCountryName(String countryName) {
		return mSQLiteDatabase.query(TABLE_CARS, carProjection,
				"country_name='" + countryName + "'", null, null, null, null);
	}

	/**
	 * 按国家获得车辆品牌数
	 * 
	 * @param countryName
	 * @return
	 */
	public int getCountCarsByCountryName(String countryName) {
		int count = 0;
		String sql = "select count(*) from cars where country_name='"
				+ countryName + "'";
		Cursor cursor = mSQLiteDatabase.rawQuery(sql, null);
		if (cursor.moveToFirst()) {
			count = cursor.getInt(0);
		}
		// 关闭游标
		cursor.close();
		return count;
	}

	/**
	 * 同步更新cars里countrys字段信息
	 * 
	 * @param sql
	 * @param Args
	 */
	public void updateSyncData(String sql, Object[] Args) {
		mSQLiteDatabase.execSQL(sql, Args);
	}

}
